
<?php
    $strSQL = "SELECT tblOrderHeader.*
					, tblOrderHeader.ID OrderHeaderID	
					, DATE_FORMAT(tblOrderHeader.OrderDate, '%Y') OrderYear
					, tblProduct.Product
					, tblProduct.ID ProductID
					, tblProduct.QuantityPerUnit
					, tblProduct.OwnProductCode
					, tblProduct.Supplier
					, tblProduct.SupplierProductID
					, tblProduct.CostPrice
					, tblProduct.CostPrice / ((tblVatRate.VatRate/100)+1) NetCostPrice
					, (tblProduct.CostPrice * Quantity) NetCostTotal
					, tblProduct.FullPrice
					, tblProduct.UploadedPicture
					, tblOrderDetails.ID
					, tblOrderDetails.OrderHeader
					, tblOrderDetails.VatRate	 VatID
					, sum(tblOrderDetails.Quantity) Quantity
					, tblOrderDetails.UnitPrice	
					, tblOrderStatus.OrderStatus
					, Quantity*tblOrderDetails.unitprice Amount
					, tblVatRate.VatRate VatRate
					, tblOrderDetails.UnitPrice / ((tblVatRate.VatRate/100)+1) NetPrice
					, SUM((tblOrderDetails.UnitPrice - (tblOrderDetails.UnitPrice / ((tblVatRate.VatRate/100)+1)))*Quantity) VatPayable
					, SUM((tblOrderDetails.UnitPrice / ((tblVatRate.VatRate/100)+1))*Quantity) NetTotal
					, COUNT(tblProduct.ID)
					FROM tblOrderHeader 
					LEFT JOIN 
						tblOrderDetails ON
							tblOrderHeader.ID = tblOrderDetails.OrderHeader
					LEFT JOIN 
						tblProduct ON 
							tblOrderDetails.Product = tblProduct.ID
					LEFT JOIN 
						tblVatRate ON 
							tblOrderDetails.VatRate = tblVatRate.ID
					LEFT JOIN 
						tblSupplier ON 
							tblProduct.Supplier = tblSupplier.ID
					LEFT JOIN 
						tblOrderStatus ON 
							tblOrderHeader.OrderStatus = tblOrderStatus.ID ";
   
   $strJoin = " WHERE ";
   if($intOrderStatus)
		$strSQL .= $strJoin." tblOrderHeader.OrderStatus = ".$intOrderStatus; 
	
	$strSQL .= " GROUP BY tblProduct.ID
				 ORDER BY tblOrderHeader.OrderDate, tblProduct.Product;";
	$qryOrderGroupedByProduct = new MySqlResults($strSQL);
	
?>
